{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "4b70f8ec-0ae1-440a-8094-5e3006c27c87",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-11-20T12:34:31.657685Z",
     "iopub.status.busy": "2025-11-20T12:34:31.657455Z",
     "iopub.status.idle": "2025-11-20T12:34:31.659875Z",
     "shell.execute_reply": "2025-11-20T12:34:31.659477Z",
     "shell.execute_reply.started": "2025-11-20T12:34:31.657666Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 轻量级数据库\n",
    "import sqlite3\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2cefa863-25b2-47cd-af05-2392ed5412d4",
   "metadata": {
    "ExecutionIndicator": {
     "show": true
    },
    "execution": {
     "iopub.execute_input": "2025-11-20T12:34:33.112893Z",
     "iopub.status.busy": "2025-11-20T12:34:33.112668Z",
     "iopub.status.idle": "2025-11-20T12:34:33.152777Z",
     "shell.execute_reply": "2025-11-20T12:34:33.152405Z",
     "shell.execute_reply.started": "2025-11-20T12:34:33.112879Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x7f31f74c3140>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = sqlite3.connect(\"company.db\")\n",
    "# 数据库操作的句柄\n",
    "cursor = conn.cursor()\n",
    "\n",
    "cursor.execute(\"\"\"\n",
    "CREATE TABLE IF NOT EXISTS employees (\n",
    "    id INTEGER PRIMARY KEY,\n",
    "    name TEXT,\n",
    "    department TEXT,\n",
    "    salary INTEGER\n",
    ")\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "08fbc36f-9e94-4a0b-ab2b-51e0855773b4",
   "metadata": {
    "ExecutionIndicator": {
     "show": true
    },
    "execution": {
     "iopub.execute_input": "2025-11-20T12:34:46.023325Z",
     "iopub.status.busy": "2025-11-20T12:34:46.023124Z",
     "iopub.status.idle": "2025-11-20T12:34:46.062146Z",
     "shell.execute_reply": "2025-11-20T12:34:46.061743Z",
     "shell.execute_reply.started": "2025-11-20T12:34:46.023311Z"
    },
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "sample_data = [\n",
    "    (6, \"超人\", \"开发部\", 32000),\n",
    "    (7, \"张三\", \"销售部\", 20000),\n",
    "    (8, \"李四\", \"开发部\", 33000),\n",
    "    (9, \"王五\", \"销售部\", 15000),\n",
    "]\n",
    "cursor.executemany('INSERT INTO employees VALUES(?,?,?,?)',sample_data)\n",
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "3028c3f0-4ee8-4b61-ab3e-f1d960fae550",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-11-20T12:50:56.172930Z",
     "iopub.status.busy": "2025-11-20T12:50:56.172732Z",
     "iopub.status.idle": "2025-11-20T12:50:56.613096Z",
     "shell.execute_reply": "2025-11-20T12:50:56.612664Z",
     "shell.execute_reply.started": "2025-11-20T12:50:56.172915Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "from openai import OpenAI\n",
    "client = OpenAI(\n",
    "    api_key='sk-a9e653be8055418db31e07fbdc625db7',\n",
    "    base_url='https://api.deepseek.com/v1'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "2ab50c0e-6749-4027-851d-de18dc47faa8",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-11-20T12:56:57.852994Z",
     "iopub.status.busy": "2025-11-20T12:56:57.852765Z",
     "iopub.status.idle": "2025-11-20T12:56:57.864351Z",
     "shell.execute_reply": "2025-11-20T12:56:57.863981Z",
     "shell.execute_reply.started": "2025-11-20T12:56:57.852975Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据库Schema:\n",
      "CREATE TABLE EMPLOYEES (\n",
      "id INTEGER\n",
      "name TEXT\n",
      "department TEXT\n",
      "salary INTEGER\n",
      ")\n"
     ]
    }
   ],
   "source": [
    "# 通过table_info 拿到employees 表的描述\n",
    "# llm 生成sql 提供上下文\n",
    "schema = cursor.execute(\"PRAGMA table_info(employees)\").fetchall()\n",
    "schema_str = \"CREATE TABLE EMPLOYEES (\\n\" + \"\\n\".join([f\"{col[1]} {col[2]}\" for col in schema]) + \"\\n)\"\n",
    "print(\"数据库Schema:\")\n",
    "print(schema_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "15817d51-4b84-4845-8116-100dfdb4d666",
   "metadata": {
    "ExecutionIndicator": {
     "show": true
    },
    "execution": {
     "iopub.execute_input": "2025-11-20T13:29:35.349868Z",
     "iopub.status.busy": "2025-11-20T13:29:35.349662Z",
     "iopub.status.idle": "2025-11-20T13:30:16.520649Z",
     "shell.execute_reply": "2025-11-20T13:30:16.520212Z",
     "shell.execute_reply.started": "2025-11-20T13:29:35.349853Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "生成的sql查询：\n",
      "ChatCompletionMessage(content=\"SELECT name, salary FROM EMPLOYEES WHERE department = '开发部';\", refusal=None, role='assistant', annotations=None, audio=None, function_call=None, tool_calls=None, reasoning_content='首先，用户提供了一个数据库的Schema：\\n\\nCREATE TABLE EMPLOYEES (\\nid INTEGER\\nname TEXT\\ndepartment TEXT\\nsalary INTEGER\\n)\\n\\n问题是要输出一个SQL查询来回答：\"开发部部门员工的姓名和工资是多少？\"\\n\\n问题中的\"开发部\"可能是一个部门名称。在Schema中，department列是TEXT类型，所以应该查询department为\\'开发部\\'的记录。\\n\\n查询应该返回员工的姓名(name)和工资(salary)。\\n\\nSQL查询的基本结构是SELECT语句，用于选择特定的列，并使用WHERE子句来过滤条件。\\n\\n所以，查询应该是：\\n\\nSELECT name, salary\\nFROM EMPLOYEES\\nWHERE department = \\'开发部\\';\\n\\n注意：在SQL中，字符串值需要用单引号括起来，所以\\'开发部\\'。\\n\\n用户说：\"只输出SQL查询,不要输出任何其他内容，也不要带任何格式。\" 所以我应该只输出SQL查询本身，没有额外的文本或格式。\\n\\n确保查询是正确的：选择name和salary列，从EMPLOYEES表，条件是department等于\\'开发部\\'。\\n\\n在Schema中，列名是name、department、salary，都是正确的。\\n\\n最后，输出应该只是SQL查询，没有分号或其他？用户没有指定是否要分号，但在SQL中，分号是可选的，但通常用于结束语句。为了完整，我会包括分号。\\n\\n用户说：\"只输出SQL查询\"，所以可能包括分号是标准的。\\n\\n现在，问题是用中文写的：\"开发部部门员工的姓名和工资是多少？\" 所以部门名称是\\'开发部\\'。\\n\\n在SQL中，字符串比较是大小写敏感的，除非指定。但这里没有指定，所以应该使用精确的字符串。假设\\'开发部\\'是准确的值。\\n\\n所以，最终的SQL查询是：\\n\\nSELECT name, salary FROM EMPLOYEES WHERE department = \\'开发部\\';\\n\\n为了可读性，我可以写成多行，但用户说\"不要带任何格式\"，可能意味着不要有额外的空格或换行？但SQL查询本身可以有空格。用户说\"不要输出任何其他内容\"，所以只输出查询文本。\\n\\n为了简单，我可以输出一行。\\n\\n但标准SQL查询可以有换行。用户没有指定，所以我会输出一个清晰的查询。\\n\\n既然用户说\"不要带任何格式\"，可能意味着不要有Markdown或其他格式，但纯文本SQL查询应该没问题。\\n\\n我会输出：\\n\\nSELECT name, salary FROM EMPLOYEES WHERE department = \\'开发部\\';\\n\\n为了确保，检查是否有拼写错误：EMPLOYEES 表名是正确的。\\n\\n在Schema中，是EMPLOYEES，所以大写或小写？在SQL中，表名和列名通常是不区分大小写的，但为了匹配，我会使用大写，如Schema中所示。\\n\\n在Schema中，是CREATE TABLE EMPLOYEES，所以表名是EMPLOYEES。\\n\\n在查询中，我可以使用大写或小写，但为了一致性，我会使用大写，如Schema。\\n\\n但用户没有指定，所以小写也可以。但Schema中表名和列名都是大写的？不，在Schema中，是id INTEGER name TEXT 等，没有指定大小写，但在SQL中，通常不区分，除非用引号。\\n\\n在提供的Schema中，是：\\n\\nCREATE TABLE EMPLOYEES (\\nid INTEGER\\nname TEXT\\ndepartment TEXT\\nsalary INTEGER\\n)\\n\\n这里，EMPLOYEES 是大写的，列名是小写的？id, name, department, salary 都是小写的。\\n\\n在SQL中，标识符可能不区分大小写，但为了准确，我应该使用Schema中的大小写。\\n\\n在Schema中，表名是EMPLOYEES（大写），列名是id, name, department, salary（小写）。\\n\\n在查询中，如果数据库不区分大小写，那么大小写无关紧要，但为了匹配，我会使用小写列名，因为Schema中是小写。\\n\\n在CREATE TABLE语句中，列名是小写的，所以最好在查询中也使用小写，以避免任何问题。\\n\\n同样，部门值\\'开发部\\'是字符串。\\n\\n所以，查询应该是：\\n\\nSELECT name, salary FROM EMPLOYEES WHERE department = \\'开发部\\';\\n\\n为了更标准，我可以使用大写关键字，但用户没有指定。\\n\\n在SQL中，关键字通常大写，但不是必须的。\\n\\n既然用户说\"不要带任何格式\"，我会使用简单的格式。\\n\\n我会输出：\\n\\nSELECT name, salary FROM EMPLOYEES WHERE department = \\'开发部\\';\\n\\n包括分号，以表示语句结束。\\n\\n现在，确认问题：\"开发部部门员工的姓名和工资是多少？\" 所以是部门为\\'开发部\\'的员工。\\n\\n在中文中，\"开发部\"可能是一个部门名称，所以直接使用。\\n\\n最终输出只有这个SQL查询。')\n"
     ]
    }
   ],
   "source": [
    "def ask_deepseek(query, schema):\n",
    "    prompt = f\"\"\"\n",
    "    这是一个数据库的Schema：\n",
    "    {schema}\n",
    "    根据这个Schema,你能输出一个SQL查询来回答以下问题吗？\n",
    "    只输出SQL查询,不要输出任何其他内容，也不要带任何格式。\n",
    "    问题：{query}\n",
    "    \"\"\"\n",
    "    # print(prompt)\n",
    "    response = client.chat.completions.create(\n",
    "        model=\"deepseek-reasoner\",\n",
    "        max_tokens=2048,\n",
    "        messages=[{\n",
    "            \"role\":\"user\",\n",
    "            \"content\":prompt\n",
    "        }]\n",
    "    )\n",
    "    return response.choices[0].message\n",
    "    \n",
    "# ask_deepseek(\"开发部门员工的姓名和工资是多少？\", schema)\n",
    "question = \"开发部部门员工的姓名和工资是多少？\"\n",
    "sql = ask_deepseek(question, schema_str)\n",
    "print(\"生成的sql查询：\")\n",
    "print(sql)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
